#!/usr/bin/env python
# -*- coding: utf-8 -*-

''' Author: Susana Messias n.º 6046 '''

import csv
import sqlite3


class createDatabase:
    '''
    Class responsible for the convertion of the csv files into sqlite database.
    '''
    @staticmethod
    def create():
        
    

        # List with the csv file names. 
        listaCsv = ['rebcsv/reb0.csv',
                    'rebcsv/reb1.csv',
                    'rebcsv/reb2.csv',
                    'rebcsv/reb3.csv',
                    'rebcsv/reb4.csv',
                    'rebcsv/reb5.csv',
                    'rebcsv/reb6.csv',
                    'rebcsv/reb7.csv',
                    'rebcsv/reb8.csv',
                    'rebcsv/reb9.csv',
                    'rebcsv/reb10.csv']
        lista = []
        nomes = []

        conexao = sqlite3.connect("tabela.db")
        c = conexao.cursor()

        ######## For not duplicate the table #########
        c.execute('''drop table if exists tabela''')
        conexao.commit()
        #############################################


        c.execute('''create table if not exists tabela (Codigo_do_Docente text,
                                                        Nome text,
                                                        Ultimo_Grau text,
                                                        Curso text,
                                                        Categoria text,
                                                        Regime_de_tempo text,
                                                        Ano_Lectivo text,
                                                        Tipo_de_Estabelecimento text,
                                                        Nome_do_Estabelecimento text)''')

        for csvFile in listaCsv:
            csvReader = csv.reader(open(csvFile, 'rb'), delimiter=',')
            line = 0
            
            for row in csvReader:
                lista2 = []
                for coluna in row:
                    lista2.append(str(coluna).decode('utf-8'))
                if line != 0:
                    stmt = '''insert into tabela (Codigo_do_Docente,
                    Nome,
                    Ultimo_Grau,
                    Curso,
                    Categoria,
                    Regime_de_Tempo,
                    Ano_Lectivo,
                    Tipo_de_Estabelecimento,
                    Nome_do_Estabelecimento)
                    values("{0}", "{1}", "{2}", "{3}", "{4}", "{5}", "{6}", "{7}", "{8}")'''.\
                            format(str(row[0]).replace( '"', '¨' ),
                                str(row[1]).replace( '"', '♦' ),
                                str(row[2]).replace( '"', '♦' ),
                                str(row[3]).replace( '"', '♦' ),
                                str(row[4]).replace( '"', '♦' ),
                                str(row[5]).replace( '"', '♦' ),
                                str(row[6]).replace( '"', '♦' ),
                                str(row[7]).replace( '"', '♦' ),
                                str(row[8]).replace( '"', '♦' ))
                    c.execute(stmt)

                    pass
                else:
                    nomes = lista2            
                line = 1
                pass
            pass
        # The end

        conexao.commit()
        conexao.close()
            


